package holo.gov;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.sql.*;
import java.util.Properties;

/**
 * @author Malegod_xiaofei
 * @create 2022-06-10-11:55
 */
public class Index {
    //    定义SQL 链接 预编译器
    private static DataSource dataSource = null;
    private static Connection connection = null;
    private static PreparedStatement preparedStatement = null;

    public static void main(String[] args) throws Exception {
//        select("select * from dim_data.dim_platform");
//        update("INSERT INTO \"dim_data\".\"dim_platform\"(\"platform_id\", \"platform_name\", \"platform_type\", \"en_name\", \"is_o2o\", \"createtime\", \"updatetime\") VALUES ('9996', '测试连接1111111111', NULL, NULL, 'f', '2021-05-12 12:09:39+08', NULL);\n");
//        update("update dim_data.dim_platform set platform_name = '11' where platform_id = '9996'");
//        update("delete from dim_data.dim_platform where platform_id = '9996'");
//        String[] month = {"201801", "201802", "201803", "201804", "201805", "201806", "201807", "201808", "201809", "201810", "201811", "201812"};
//        String[] month = {"201801", "201802", "201803", "201804", "201805", "201806", "201807", "201808", "201809", "201810"};
//        String[] month = {"201901", "201902", "201903", "201904", "201905", "201906", "201907", "201908", "201909", "201910", "201911", "201912"};
//        String[] month = {"202001", "202002", "202003", "202004", "202005", "202006", "202007", "202008", "202009", "202010", "202011", "202012"};
//        String[] month = {"202001", "202002", "202003", "202004", "202005", "202006", "202007", "202008", "202009", "202010", "202011", "202012", "202101", "202102", "202103", "202104", "202105", "202106", "202107", "202108", "202109", "202110", "202111", "202112", "202201", "202202", "202203", "202204"};
//        String[] month = {"202101", "202102"};
//        String[] month = {"202101", "202102", "202103", "202104", "202105", "202106", "202107", "202108"};
//        String[] month = {"202103", "202104", "202105", "202106", "202107", "202108", "202109", "202110", "202111", "202112", "202201", "202202", "202203", "202204"};
//        String[] month = {"202102", "202103", "202104", "202105", "202106", "202107", "202108", "202109", "202110", "202111", "202112"};
//        String[] month = { "202102", "202103", "202104", "202105", "202106", "202107", "202108", "202109", "202110", "202111", "202112", "202201", "202202", "202203", "202204"};
//        String[] month = {"201901", "201902", "201903", "201904", "201905", "201906", "201907", "201908", "201909", "201910", "201911", "201912", "202001", "202002", "202003", "202004", "202005", "202006", "202007", "202008", "202009", "202010", "202011", "202012", "202101", "202102", "202103", "202104", "202105", "202106", "202107", "202108", "202109", "202110", "202111", "202112", "202201", "202202", "202203", "202204"};
//        String[] month = {"201811", "201812", "201901", "201902", "201903", "201904", "201905", "201906", "201907", "201908", "201909", "201910", "201911", "201912", "202001", "202002", "202003", "202004", "202005", "202006", "202007", "202008", "202009", "202010", "202011", "202012", "202101", "202102", "202103", "202104", "202105", "202106", "202107", "202108", "202109", "202110", "202111", "202112", "202201", "202202", "202203", "202204"};
//        String[] month = {"201801", "201802", "201803", "201804", "201805", "201806", "201807", "201808", "201809", "201810", "201811", "201812", "201901", "201902", "201903", "201904", "201905", "201906", "201907", "201908", "201909", "201910", "201911", "201912", "202001", "202002", "202003", "202004", "202005", "202006", "202007", "202008", "202009", "202010", "202011", "202012", "202101", "202102", "202103", "202104", "202105", "202106", "202107", "202108", "202109", "202110", "202111", "202112", "202201", "202202", "202203", "202204"};
        String[] month = {"202201", "202202", "202203", "202204", "202205", "202206", "202207"};
        String sql = "";

//        for (int i = 0; i < month.length; i++) {
//            System.out.println("更新月份 : " + month[i]);
//            sql = "update dbadmin.o2o_allplat_shop_bdl_" + month[i] + " set region_id = '139003' where region_id = '130673';";
//            update(sql);
//            System.out.println("更新 sql :" + sql);
//        }
        System.out.println("111");
    }

    public static void select(String sql) throws SQLException {

        //获取连接
        Properties properties = new Properties();
        try {
            properties.load(ClassLoader.getSystemClassLoader().getResourceAsStream("gov/pgconnect.properties"));
            dataSource = DruidDataSourceFactory.createDataSource(properties);

        } catch (Exception e) {
            e.printStackTrace();
        }

        // 获取德鲁伊对象连接器
        if (connection == null) {
            try {
                connection = dataSource.getConnection();
                System.out.println("connection : -->" + connection);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
//        String executoresultSetQL = "select * from dim_data.dim_platform";

        preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        // 打印 resultSet 数据
        ResultSetMetaData resultSetmd = resultSet.getMetaData();
        int columnsNumber = resultSetmd.getColumnCount();
        while (resultSet.next()) {
            for (int i = 1; i <= columnsNumber; i++) {
                if (i > 1) System.out.print(",  ");
                String columnValue = resultSet.getString(i);
                System.out.print(columnValue + " " + resultSetmd.getColumnName(i));
            }
            System.out.println("");
        }
        preparedStatement.close();
    }

    public static void update(String sql) throws SQLException {

        //获取连接
        Properties properties = new Properties();
        try {
            properties.load(ClassLoader.getSystemClassLoader().getResourceAsStream("gov/pgconnect.properties"));
            dataSource = DruidDataSourceFactory.createDataSource(properties);

        } catch (Exception e) {
            e.printStackTrace();
        }

        // 获取德鲁伊对象连接器
        if (connection == null) {
            try {
                connection = dataSource.getConnection();
                System.out.println("connection : -->" + connection);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
//        String executoresultSetQL = "select * from dim_data.dim_platform";

        preparedStatement = connection.prepareStatement(sql);
        int resultSet = preparedStatement.executeUpdate();
        // 打印 resultSet 数据
        System.out.println("一共 : " + resultSet + " 条数据受影响 ! ");
        preparedStatement.close();

    }


}